Schema

接下來我們要做一些方法給 SQLite model

# rainbow/lib/rainbow/sqlite_model.rb (excerpt)
  module Rulers
    module Model
      class SQLite
        def initialize(data = nil)
          @hash = data
        end

        def self.to_sql(val)
          case val
          when NilClass
            'null'
          when Numeric
            val.to_s
          when String
            "'#{val}'"
          else
            raise "Can't change #{val.class} to SQL!"
          end 
        end
        
        def self.create(values)
          values.delete "id"
          keys = schema.keys - ["id"]
          vals = keys.map do |key|
            values[key] ? to_sql(values[key]) : "null"
          end
          DB.execute << SQL
          INSERT INTO #{table} (#{keys.join ","})
          VALUES (#{vals.join ","});
          SQL
          raw_vals = keys.map { |k| values[k] }
          data = Hash[keys.zip raw_vals]
          sql = "SELECT last_insert_rowid();"
          data["id"] = DB.execute(sql)[0][0]
          self.new data
        end
        
        def self.count
          DB.execute(<<SQL)[0][0]
          SELECT COUNT(*) FROM #{table}
          SQL
       end 
     end
   end 
 end

現在我們在資料表中建立一個物件跟欄位，然後來數一下存了多少

SQLite 的主 key 叫 id，當欄位增加資料時， id 會自動增加

我們來改一下程式碼，新增新的欄位以及秀出有哪些欄位

# best_quotes/sqlite_test.rb
    require "sqlite3"
    require "rainbow/sqlite_model"
    class MyTable < Rainbow::Model::SQLite; end
    STDERR.puts MyTable.schema.inspect
    # Create row
    mt = MyTable.create "title" => "It happened!", "posted" => 1, "body" => "It did!"
    mt = MyTable.create "title" => "I saw it!"
    puts "Count: #{MyTable.count}"

> bundle exec ruby sqlite_test.rb

執行的時候，每次都會建立兩次

尋找方法

我們來增加 find 方法

# rainbow/lib/rainbow/sqlite_model.rb 
  module Rainbow
    module Model
      def self.find(id)
        row = DB.execute <<SQL
        select #{schema.keys.join ","} from #{table} where id = #{id};
        SQL
        data = Hash[schema.keys.zip row[0]]
        self.new data
      end

      def [](name)
        @hash[name.to_s]
      end
    
      def []=(name, value)
        @hash[name.to_s] = value
      end 
    end
  end 

改下列程式碼，並把所有欄位印出

    # best_quotes/sqlite_test.rb
    require "sqlite3"
    require "rulers/sqlite_model"
    
    class MyTable < Rainbow::Model::SQLite; end
    STDERR.puts MyTable.schema.inspect
    
    # Create row
    mt = MyTable.create "title" => "I saw it again!"
    puts "Count: #{MyTable.count}"
    top_id = mt["id"].to_i
    
    (1..top_id).each do |id|
      mt_id = MyTable.find(id)
      puts "Found title #{mt_id["title"]}."
    end

跑這段程式碼，就會出現下列訊息

{"id"=>"INTEGER", "posted"=>"INTEGER", "title"=>"VARCHAR(30)", "body"=>"VARCHAR(32000)"}
    Count: 6
    Found title It happened!.
    Found title I saw it!.
    Found title It happened!.
    Found title I saw it!.
    Found title I saw it again!.
    Found title I saw it again!.
    Found title I saw it!.

這裡會根據 ID 創建出資料庫物件，並且把 title 印出，就像 ActiveRecord Lite 做的

因為要把資料從資料庫撈出來，但因為沒有自動轉換，我們必須手動 ID 轉換成字串

執行方法及例外狀況設定

接下來我們要把物件存起來，我們來看一下這一段程式碼

我們都知道 Ruby 有 rescue

而 rescue false 是指 當 exception 發生時，他會直接回傳 false

ActiveRecord 裡有一個方法叫 save

當失敗的時候會呼叫一個 exception

# rainbow/lib/rainbow/sqlite_model.rb
    module Rainbow
      module Model
        class SQLite
        
          def save!
            fields = @hash.map do |k, v|
              "#{k}=#{self.class.to_sql(v)}" end.join ","
              DB.execute << SQL
              UPDATE #{self.class.table}
              SET #{fields} WHERE id = #{@hash["id"]}
              SQL
              true 
            end
          
            def save
              self.save! rescue false
            end 
          end
        end
      end 
    end

# best_quotes/sqlite_test.rb
  require "sqlite3"
  require "rainbow/sqlite_model"
  class MyTable < Rainbow::Model::SQLite; end
      
  # Create row
  mt = MyTable.create "title" => "I saw it again!"
  mt["title"] = "I really did!"
  mt.save!
  mt2 = MyTable.find mt["id"]
  puts "Title: #{mt2["title"]}"

> bundle exec ruby sqlite_test.rb

當你看到 Title: I really did!
代表你已經成功修改了 title